package com.css.zfzx.sjcj.modules.statistics.qhRiskLevelMapPoint.repository.impl;

import com.css.zfzx.sjcj.common.vo.DivisionVo;
import com.css.zfzx.sjcj.modules.pojo.PcStatisticsFillVO;
import com.css.zfzx.sjcj.modules.pojo.QueryVo;
import com.css.zfzx.sjcj.modules.statistics.qhProbabilityEarthquakeDanger.repository.entity.QhProbabilityEarthquakeDangerVO;
import com.css.zfzx.sjcj.modules.statistics.qhRiskLevelMapPoint.repository.entity.QhRiskLevelMapPointEntity;
import com.css.zfzx.sjcj.modules.statistics.qhRiskLevelMapPoint.repository.QhRiskLevelMapPointRepository;
import com.css.zfzx.sjcj.modules.statistics.qhRiskLevelMapPoint.repository.entity.QhRiskLevelMapPointVO;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @Description: pc-statistics
 * @Author: lhl
 * @CreateDate: 2021/5/13 11:17
 */
@Repository
public class QhRiskLevelMapPointRepositoryImpl implements QhRiskLevelMapPointRepository {
    @PersistenceContext
    private EntityManager em;

    private static final Logger log = LoggerFactory.getLogger(QhRiskLevelMapPointRepositoryImpl.class);

    @Override
    public List<PcStatisticsFillVO> findCreateTime(LocalDateTime createTime) {
        StringBuilder sql = new StringBuilder("select a.province,a.city,a.area,a.create_user,a.create_time," +
                "a.affiliated_file_name,a.total,b.report from (select province,city,area,count(*) total,create_user," +
                "create_time,affiliated_file_name from qh_risk_level_map_point where is_valid='1'");
        if (createTime!=null) {
            sql.append(" and create_time > :createTime1");
        }else {
            sql.append(" and create_time is not null");
        }
        sql.append(" and update_time is null");
        sql.append(" group by province,city,area order by create_time desc) a LEFT JOIN (select province,city," +
                "area,count(is_report_pg) report from qh_risk_level_map_point where is_valid='1' and " +
                "is_report_pg='1'");
        if (createTime!=null) {
            sql.append(" and create_time > :createTime2");
        }else {
            sql.append(" and create_time is not null");
        }
        sql.append(" and update_time is null");
        sql.append(" group by province,city,area order by create_time desc) b ON a.province=b.province and " +
                "a.city=b.city and a.area=b.area");
        Query query = this.em.createNativeQuery(sql.toString());
        // 时间
        if (createTime!=null) {
            query.setParameter("createTime1", createTime);
        }
        if (createTime!=null) {
            query.setParameter("createTime2", createTime);
        }
        List<Object[]> list = query.getResultList();
        return getVOList(list,1);
    }
    private List<PcStatisticsFillVO> getVOList(List<Object[]> list,int identify){
        List<PcStatisticsFillVO> voList=new ArrayList<>();
        for (Object[] objects:list){
            PcStatisticsFillVO vo=new PcStatisticsFillVO();
            vo.setProvince(String.valueOf(objects[0]));
            vo.setCity(String.valueOf(objects[1]));
            vo.setArea(String.valueOf(objects[2]));
            vo.setCreateUser(String.valueOf(objects[3]));
            vo.setCreateTime((Date) objects[4]);
            vo.setAttachmentName(String.valueOf(objects[5]));
            vo.setTotalCount((BigInteger)objects[6]);
            vo.setReportCount((BigInteger)objects[7]);
            if (identify==2){
                vo.setUpdateUser(String.valueOf(objects[8]));
                vo.setUpdateTime((Date) objects[9]);
                vo.setIsValid(String.valueOf(objects[10]));
            }
            voList.add(vo);
        }
        return voList;
    }
    @Override
    public List<PcStatisticsFillVO> findUpdateTime(LocalDateTime updateTime) {
        StringBuilder sql = new StringBuilder("select a.province,a.city,a.area,a.create_user,a.create_time," +
                "a.affiliated_file_name,a.total,b.report,a.update_user,a.update_time,a.is_valid from (select " +
                "province,city,area,count(*) total,create_user,create_time,affiliated_file_name,update_user," +
                "update_time,is_valid from qh_risk_level_map_point where 1=1");
        if (updateTime!=null) {
            sql.append(" and update_time > :updateTime1");
        }else {
            sql.append(" and update_time is not null");
        }
        sql.append(" group by province,city,area order by update_time desc) a LEFT JOIN (select province,city," +
                "area,count(is_report_pg) report from qh_risk_level_map_point where is_report_pg='1'");
        if (updateTime!=null) {
            sql.append(" and update_time > :updateTime2");
        }else {
            sql.append(" and update_time is not null");
        }
        sql.append(" group by province,city,area order by update_time desc) b ON a.province=b.province and " +
                "a.city=b.city and a.area=b.area");
        Query query = this.em.createNativeQuery(sql.toString());
        // 时间
        if (updateTime!=null) {
            query.setParameter("updateTime1", updateTime);
        }
        if (updateTime!=null) {
            query.setParameter("updateTime2", updateTime);
        }
        List<Object[]> list = query.getResultList();
        return getVOList(list,2);
    }

    @Override
    public List<DivisionVo> count(int identify) {
        StringBuilder sql = new StringBuilder("select province,city,area from qh_risk_level_map_point");
        StringBuilder whereSql = new StringBuilder(" where is_valid = 1 ");
        if (identify==1){
            whereSql.append(" and is_report_pg = '1'");
        }else if (identify==2){
            whereSql.append(" and (is_report_pg = '0' or is_report_pg is null)");
        }
        whereSql.append(" group by province,city,area");
        Query query = this.em.createNativeQuery(sql.append(whereSql).toString());

        List<Object[]> resultList = query.getResultList();
        List<DivisionVo> voList=new ArrayList<>();
        for (Object[] objects:resultList){
            DivisionVo vo=new DivisionVo();
            vo.setProvinceName(String.valueOf(objects[0]));
            vo.setCityName(String.valueOf(objects[1]));
            vo.setAreaName(String.valueOf(objects[2]));
            voList.add(vo);
        }
        return voList;
    }

    @Override
    public Page<QhRiskLevelMapPointVO>find(QueryVo queryVo, String userId, int curPage, int pageSize, String sort,
                                           String order) {
        StringBuilder sql = new StringBuilder("select province,city,area,a,b,c,create_user,create_time,quality_status,quality_comment,is_report_pg" +
                " ");
        if ("格尔木市".equals(queryVo.getArea())){
            sql.append(" from qh_risk_level_map_point_geermu ");
        }else if ("盈江县".equals(queryVo.getArea())){
            sql.append(" from qh_risk_level_map_point_yunnan ");
        }else if ("江达县".equals(queryVo.getArea())){
            sql.append(" from qh_risk_level_map_point_xizang ");
        }else{
            sql.append(" from qh_risk_level_map_point ");
        }
        StringBuilder whereSql = new StringBuilder(" where is_valid = 1 ");
        if (StringUtils.isNotEmpty(queryVo.getProvince())){
            whereSql.append(" and province =:province");
        }
        if (StringUtils.isNotEmpty(queryVo.getCity())){
            whereSql.append(" and city =:city");
        }
        if (StringUtils.isNotEmpty(queryVo.getArea())){
            whereSql.append(" and area =:area");
        }
        if (StringUtils.isNotEmpty(queryVo.getStartTime())){
            whereSql.append(" and create_time >=:startTime");
        }
        if (StringUtils.isNotEmpty(queryVo.getEndTime())){
            whereSql.append(" and create_time <=:endTime");
        }
        if (!StringUtils.isEmpty(sort)) {
            whereSql.append(" order by " + sort + " " + order);
        }
        //whereSql.append(" limit " + (curPage-1)*pageSize + "," + pageSize);
        Query query = this.em.createNativeQuery(sql.append(whereSql).toString());
        StringBuilder countSql = new StringBuilder("select count(*) from qh_risk_level_map_point ");
        Query countQuery = this.em.createNativeQuery(countSql.append(whereSql).toString());
        if (StringUtils.isNotEmpty(queryVo.getProvince())){
            query.setParameter("province",queryVo.getProvince());
            countQuery.setParameter("province",queryVo.getProvince());
        }
        if (StringUtils.isNotEmpty(queryVo.getCity())){
            query.setParameter("city",queryVo.getCity());
            countQuery.setParameter("city",queryVo.getCity());
        }
        if (StringUtils.isNotEmpty(queryVo.getArea())){
            query.setParameter("area",queryVo.getArea());
            countQuery.setParameter("area",queryVo.getArea());
        }
        if (StringUtils.isNotEmpty(queryVo.getStartTime())){
            query.setParameter("startTime",queryVo.getStartTime());
            countQuery.setParameter("startTime",queryVo.getStartTime());
        }
        if (StringUtils.isNotEmpty(queryVo.getEndTime())){
            query.setParameter("endTime",queryVo.getEndTime());
            countQuery.setParameter("endTime",queryVo.getEndTime());
        }
        Pageable pageable = PageRequest.of(curPage - 1, pageSize);
        query.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());
        query.setMaxResults(pageable.getPageSize());
        List<Object[]> list = query.getResultList();
        BigInteger count = (BigInteger) countQuery.getSingleResult();
        Page<QhRiskLevelMapPointVO> pcPage = new PageImpl<>(getVOs(list), pageable, count.longValue());
        return pcPage;
    }
    private List<QhRiskLevelMapPointVO> getVOs(List<Object[]> list){
        List<QhRiskLevelMapPointVO> vos=new ArrayList<>();
        for (Object[] objects : list){
            QhRiskLevelMapPointVO vo=new QhRiskLevelMapPointVO();
            vo.setProvince(String.valueOf(objects[0]));
            vo.setCity(String.valueOf(objects[1]));
            vo.setArea(String.valueOf(objects[2]));
            vo.setA((BigDecimal) objects[3]);
            vo.setB((BigDecimal) objects[4]);
            vo.setC(String.valueOf(objects[5]));
            vo.setCreateUser(String.valueOf(objects[6]));
            vo.setCreateTime((Date) objects[7]);
            vo.setQualityStatus(String.valueOf(objects[8]));
            vo.setQualityComment(String.valueOf(objects[9]));
            vo.setIsReportPg(String.valueOf(objects[10]));
            vos.add(vo);
        }
        return vos;
    }
    @Override
    public List<QhRiskLevelMapPointVO> findNoPage(String userId) {
        StringBuilder sql = new StringBuilder("select province,city,area,a,b,c,create_user,create_time,quality_status,quality_comment,is_report_pg " +
                " from qh_risk_level_map_point");
        StringBuilder whereSql = new StringBuilder(" where is_valid = 1 ");
        Query query = this.em.createNativeQuery(sql.append(whereSql).toString());
        List<Object[]> list = query.getResultList();
        return getVOs(list);
    }

    @Override
    public List<QhRiskLevelMapPointEntity> findNoPages(QueryVo queryVo,String userId) {
        StringBuilder sql = new StringBuilder("select uuid,province,city,area,affiliated_file_name,a,b,c,object_code," +
                "is_valid,partition_flag,create_user,create_time,update_user,update_time," +
                "ST_AsText(geo_point) as geo_point,extend1,extend2,extend3,geo_multipoint,quality_status," +
                "quality_comment,is_report_pg");
        if ("格尔木市".equals(queryVo.getArea())){
            sql.append(" from qh_risk_level_map_point_geermu ");
        }else if ("盈江县".equals(queryVo.getArea())){
            sql.append(" from qh_risk_level_map_point_yunnan ");
        }else if ("江达县".equals(queryVo.getArea())){
            sql.append(" from qh_risk_level_map_point_xizang ");
        }else if ("库车市".equals(queryVo.getArea())){
            sql.append(" from qh_risk_level_map_point_xinjiang ");
        }else{
            sql.append(" from qh_risk_level_map_point ");
        }
        StringBuilder whereSql = new StringBuilder(" where is_valid = 1 ");
        if (StringUtils.isNotEmpty(queryVo.getProvince())){
            whereSql.append(" and province =:province");
        }
        if (StringUtils.isNotEmpty(queryVo.getCity())){
            whereSql.append(" and city =:city");
        }
        if (StringUtils.isNotEmpty(queryVo.getArea())){
            whereSql.append(" and area =:area");
        }
        if (StringUtils.isNotEmpty(queryVo.getStartTime())){
            whereSql.append(" and create_time >=:startTime");
        }
        if (StringUtils.isNotEmpty(queryVo.getEndTime())){
            whereSql.append(" and create_time <=:endTime");
        }
        //whereSql.append(" and uuid='1ffc3a04e6c645be8f465ce5823d11eb'");
        //whereSql.append(" and (quality_status!='1' or quality_status is null)");
        Query query = this.em.createNativeQuery(sql.append(whereSql).toString(),QhRiskLevelMapPointEntity.class);
        if (StringUtils.isNotEmpty(queryVo.getProvince())){
            query.setParameter("province",queryVo.getProvince());
        }
        if (StringUtils.isNotEmpty(queryVo.getCity())){
            query.setParameter("city",queryVo.getCity());
        }
        if (StringUtils.isNotEmpty(queryVo.getArea())){
            query.setParameter("area",queryVo.getArea());
        }
        if (StringUtils.isNotEmpty(queryVo.getStartTime())){
            query.setParameter("startTime",queryVo.getStartTime());
        }
        if (StringUtils.isNotEmpty(queryVo.getEndTime())){
            query.setParameter("endTime",queryVo.getEndTime());
        }
        List<QhRiskLevelMapPointEntity> list = query.getResultList();
        return list;
    }

    @Override
    public List<String> findAttachmentName() {
        Query query = this.em.createNativeQuery("select affiliated_file_name from qh_risk_level_map_point where is_valid = 1");
        List<String> list = query.getResultList();
        return list;
    }
}
